# -*- coding: utf-8 -*-
# @Author: Cursor 
# @Date: 2025-01-22
# @Last Modified by: Tim Liu
# @Last Modified time: 2025-01-22

# @Description: SQL Conversation Service, manages the conversation between the user and the bot using SQL.

from utils.db_getter import DBGetter
from datetime import datetime
from langchain_community.chat_message_histories import SQLChatMessageHistory
from config.settings import SQLALCHEMY_MESSAGE_STORE
from crewplus.apps.conversation.schemas import Conversation
from .conversation_service import ConversationService

class SQLConversationService(ConversationService, DBGetter):
    """
    SQL Conversation Service, manages the conversation between the user and the bot (session separation) using SQL.
    """

    def add_message(self, conversation_id: str, message: str, type: str = "user"):
        # add user message to the conversation
        chat_history = SQLChatMessageHistory(
            session_id=conversation_id, connection_string=SQLALCHEMY_MESSAGE_STORE
        )
        
        if type == "user":
            chat_history.add_user_message(message)
        else:
            chat_history.add_ai_message(message)
        
        return chat_history

    def get_history_messages(self, conversation_id: str):
        self.conn_mysql()
        
        # get conversation history from the database
        sql_query = ""
        if conversation_id:
            sql_query = f"""SELECT * from message_store WHERE session_id = '{conversation_id}' LIMIT 20 """
        else:
            sql_query = "SELECT * from message_store LIMIT 20 "
        
        self.mysql_cursor.execute(sql_query)
        result = self.mysql_cursor.fetchall()
        self.close_mysql()
        
        chat_history = []

        # convert json to string and add to chat history 
        for item in result:
            try:
                message = self.json_to_string(item[2])
                chat_history.extend([message])
            except ValueError as e:
                pass
            
        return chat_history